---
id: dbcontext-batch
title: 9.9 批量操作
sidebar_label: 9.9 批量操作
---

## 9.9.1 关于批量操作

`Furion` 框架中，默认只提供小数据（100 条 以内）批量数据操作，如果需要更大的数据批量处理，推荐使用第三方包 [Zack.EFCore.Batch](https://github.com/yangzhongke/Zack.EFCore.Batch)，支持和 `Furion` 无缝衔接。

## 9.9.2 `Zack.EFCore.Batch` 使用

### 9.9.2.1 安装对应的数据库 `Nuget` 包

- `MSSQL`：`Zack.EFCore.Batch.MSSQL`
- `MySql`：`Zack.EFCore.Batch.MySQL.Pomelo`
- `Npgsql`：`Zack.EFCore.Batch.Npgsql`
- `Oracle`：`Zack.EFCore.Batch.Oracle`
- `Sqlite`：`Zack.EFCore.Batch.Sqlite`

### 9.9.2.2 注册并配置服务

```cs {3,5}
services.AddDatabaseAccessor(options =>
{
    options.AddDbPool<DefaultDbContext>(providerName: default, optionBuilder: opt =>
    {
        opt.UseBatchEF_Sqlite();    // SQlite 数据库包
    });
});
```

### 9.9.2.3 基本使用

```cs
// 批量更新
await repository.Context.BatchUpdate<Book>()
    .Set(b => b.Price, b => b.Price + 3)
    .Set(b => b.Title, b => s)
    .Set(b => b.AuthorName,b=>b.Title.Substring(3,2)+b.AuthorName.ToUpper())
    .Set(b => b.PubTime, b => DateTime.Now)
    .Where(b => b.Id > n || b.AuthorName.StartsWith("Zack"))
    .ExecuteAsync();

// 批量删除
await repository.Context.DeleteRangeAsync<Book>(b => b.Price > n || b.AuthorName == "zack yang");
```

## 9.9.3 `EFCore.BulkExtensions` 使用

通过 `Nuget` 安装 `EFCore.BulkExtensions` 包即可。

### 9.9.3.1 常见批量操作

```cs
// 批量插入
repository.Context.BulkInsert(entitiesList);
repository.Context.BulkInsertAsync(entitiesList);

// 批量更新
repository.Context.BulkUpdate(entitiesList);
repository.Context.BulkUpdateAsync(entitiesList);

// 批量删除
repository.Context.BulkDelete(entitiesList);
repository.Context.BulkDeleteAsync(entitiesList);

// 批量插入或更新
repository.Context.BulkInsertOrUpdate(entitiesList);
repository.Context.BulkInsertOrUpdateAsync(entitiesList);

// 批量插入或更新或删除
repository.Context.BulkInsertOrUpdateOrDelete(entitiesList);
repository.Context.BulkInsertOrUpdateOrDeleteAsync(entitiesList);

// 批量读取多个实体
repository.Context.BulkRead(entitiesList);
repository.Context.BulkReadAsync(entitiesList);

// 批量清空表（慎用！！！！！）
repository.Context.Truncate<Entity>();
repository.Context.TruncateAsync<Entity>();
```

### 9.9.3.2 查询后批量操作

```cs
// 根据条件批量删除
repository.Where(a => a.ItemId >  500).BatchDelete();
await repository.Where(a => a.ItemId >  500).BatchDeleteAsync();

// 根据条件批量更新
repository.Where(a => a.ItemId <= 500).BatchUpdate(a => new Item { Quantity = a.Quantity + 100 });
repository.Where(a => a.ItemId <= 500).BatchUpdate(new Item { Description = "Updated" });
await repository.Where(a => a.ItemId <= 500).BatchUpdateAsync(new Item { Description = "Updated" });

// 批量更新指定列
var updateColumns = new List<string> { nameof(Item.Quantity) };
var q = repository.Where(a => a.ItemId <= 500);
int affected = q.BatchUpdate(new Item { Description = "Updated" }, updateColumns);
```

### 9.9.3.3 批量操作性能

| Operations\Rows   |   100,000 EF |   100,000 EFBulk |   1,000,000 EFBulk |
| ----------------- | -----------: | ---------------: | -----------------: |
| Insert            |      38.98 s |           2.10 s |            17.99 s |
| Update            |     109.25 s |           3.96 s |            31.45 s |
| Delete            |       7.26 s |           2.04 s |            12.18 s |
| ----------------- | ------------ | ---------------- | ------------------ |
| **Together**      |      70.70 s |           5.88 s |            56.84 s |

## 9.9.4 反馈与建议

:::note 与我们交流

给 Furion 提 [Issue](https://gitee.com/dotnetchina/Furion/issues/new?issue)。

:::

---

:::note 了解更多

想了解更多 `EFCore.BulkExtensions` 知识可查阅 [EFCore.BulkExtensions 开源仓库](https://github.com/borisdj/EFCore.BulkExtensions)。

:::
